package com.taskschecker.db;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DBManager {
	private DBHelper helper;
	private SQLiteDatabase db;

	public DBManager(Context context) {
		helper = new DBHelper(context);
		db = helper.getWritableDatabase();
	}

	/**
	 * add taskgroup and task info
	 * 
	 * @param taskgroup
	 *            and taskinfos
	 */
	public void addTaskGroupAndTaskInfo(TaskGroup taskgroup,
			List<TaskInfo> taskinfos) {
		db.beginTransaction();
		int id = 0;
		try {
			Cursor c = db
					.rawQuery(
							"select CASE WHEN max(groupid) IS NULL THEN 1 ELSE max(groupid)+1 END as groupid from taskgroup",
							null);
			while (c.moveToNext()) {
				id = c.getInt(c.getColumnIndex("groupid"));
			}
			c.close();

			db.execSQL(
					"INSERT INTO taskgroup VALUES(null, ?, ?, ?, ?, ?, ?)",
					new Object[] { taskgroup.getContent(),
							taskgroup.getStartdate(), taskgroup.getEnddate(),
							taskgroup.getMemo(), taskgroup.getRepeatetype(),
							taskgroup.getRepeatevalue() });

			for (TaskInfo taskinfo : taskinfos) {
				db.execSQL(
						"INSERT INTO taskinfo VALUES(null, ?, ?, ?, ?, ?)",
						new Object[] { taskinfo.getContent(),
								taskinfo.getDate(), taskinfo.getMemo(),
								taskinfo.getCompleteness(), id });
			}
			db.setTransactionSuccessful();

		} finally {
			db.endTransaction();
		}
	}

	/**
	 * update taskinfo'completeness
	 * 
	 * @param taskinfo
	 */
	public void updateCompleteness(TaskInfo taskinfo) {

		db.beginTransaction();
		try {
			String update_SQL = "update taskinfo set " + "completeness=\""
					+ taskinfo.getCompleteness() + "\" where taskid="
					+ taskinfo.getTaskid();
			db.execSQL(update_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * delete taskinfo
	 * 
	 * @param taskinfo
	 */
	public void deleteTaskInfo(TaskInfo taskinfo) {
		db.beginTransaction();
		try {
			String delete_SQL = "delete from taskinfo where taskid="
					+ taskinfo.getTaskid();
			db.execSQL(delete_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * update taskgroup and insert taskinfo
	 * 
	 * @param taskgroup
	 *            , List<TaskInfo>, date
	 */
	public void updateTaskGroupAndAddTaskInfo(TaskGroup taskgroup, String date,
			List<TaskInfo> taskinfos) {
		db.beginTransaction();
		try {
			String update_SQL = "update taskgroup set " + "content=\""
					+ taskgroup.getContent() + "\", startdate="
					+ taskgroup.getStartdate() + ", enddate="
					+ taskgroup.getEnddate() + ", memo=\""
					+ taskgroup.getMemo() + "\", repeatetype="
					+ taskgroup.getRepeatetype() + ", repeatevalue=\""
					+ taskgroup.getRepeatevalue() + "\" where groupid="
					+ taskgroup.getTaskgroupid();
			db.execSQL(update_SQL);

			String delete_SQL = "delete from taskinfo where groupid="
					+ taskgroup.getTaskgroupid() + " and date>=" + date
					+ " and completeness =0";
			db.execSQL(delete_SQL);

			for (TaskInfo taskinfo : taskinfos) {
				db.execSQL(
						"INSERT INTO taskinfo VALUES(null, ?, ?, ?, ?, ?)",
						new Object[] { taskinfo.getContent(),
								taskinfo.getDate(), taskinfo.getMemo(),
								taskinfo.getCompleteness(),
								taskgroup.getTaskgroupid() });
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * delete taskinfo by groupID
	 * 
	 * @param taskgroupid
	 *            and date
	 */
	public void stopTaskGroup(String taskgroupid, Integer date) {
		db.beginTransaction();
		try {
			db.execSQL("delete from taskinfo where groupid = " + taskgroupid
					+ " and date >= " + date + " and completeness = 0");
			db.execSQL("delete from taskgroup where groupid = " + taskgroupid);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * query oneday's taskinfo, return list
	 * 
	 * @param date
	 * @return List<TaskInfo>
	 */
	public List<TaskInfo> getTaskInfoListByDate(Integer date) {
		List<TaskInfo> taskinfos = new ArrayList<TaskInfo>();
		Cursor c = db.rawQuery("SELECT * FROM taskinfo WHERE date = " + date,
				null);
		while (c.moveToNext()) {
			TaskInfo taskinfo = new TaskInfo();
			taskinfo.setTaskid(c.getInt(c.getColumnIndex("taskid")));
			taskinfo.setContent(c.getString(c.getColumnIndex("content")));
			taskinfo.setDate(c.getInt(c.getColumnIndex("date")));
			taskinfo.setMemo(c.getString(c.getColumnIndex("memo")));
			taskinfo.setCompleteness(c.getInt(c.getColumnIndex("completeness")));
			taskinfo.setGroupid(c.getInt(c.getColumnIndex("groupid")));
			taskinfos.add(taskinfo);
		}
		c.close();
		return taskinfos;
	}

	/**
	 * query oneday's taskinfo, return list
	 * 
	 * @param date
	 * @return List<TaskInfo>
	 */
	public List<TaskInfo> getTaskInfoListByDate(Integer from, Integer to) {
		List<TaskInfo> taskinfos = new ArrayList<TaskInfo>();
		Cursor c = db.rawQuery("SELECT * FROM taskinfo WHERE date between "
				+ from + " and " + to, null);
		while (c.moveToNext()) {
			TaskInfo taskinfo = new TaskInfo();
			taskinfo.setTaskid(c.getInt(c.getColumnIndex("taskid")));
			taskinfo.setContent(c.getString(c.getColumnIndex("content")));
			taskinfo.setDate(c.getInt(c.getColumnIndex("date")));
			taskinfo.setMemo(c.getString(c.getColumnIndex("memo")));
			taskinfo.setCompleteness(c.getInt(c.getColumnIndex("completeness")));
			taskinfo.setGroupid(c.getInt(c.getColumnIndex("groupid")));
			taskinfos.add(taskinfo);
		}
		c.close();
		return taskinfos;
	}

	/**
	 * get taskgroup info
	 * 
	 * @param groupid
	 * @return taskgroup
	 */
	public TaskGroup getTaskGroupById(String groupid) {
		TaskGroup taskgroup = new TaskGroup();
		Cursor c = db.rawQuery("SELECT * FROM taskgroup WHERE groupid="
				+ groupid, null);
		while (c.moveToNext()) {
			taskgroup.setContent(c.getString(c.getColumnIndex("content")));
			taskgroup.setStartdate(c.getInt(c.getColumnIndex("startdate")));
			taskgroup.setEnddate(c.getInt(c.getColumnIndex("enddate")));
			taskgroup.setMemo(c.getString(c.getColumnIndex("memo")));
			taskgroup.setRepeatetype(c.getInt(c.getColumnIndex("repeatetype")));
			taskgroup.setRepeatevalue(c.getString(c
					.getColumnIndex("repeatevalue")));
		}
		c.close();
		return taskgroup;
	}

	/**
	 * query taskgroup list, return list
	 * 
	 * @return List<TaskGroup>
	 */
	public List<TaskGroup> taskGroupList() {
		List<TaskGroup> taskgroups = new ArrayList<TaskGroup>();
		Cursor c = db.rawQuery("SELECT * FROM taskgroup order by groupid desc",
				null);
		while (c.moveToNext()) {
			TaskGroup taskgroup = new TaskGroup();
			taskgroup.setTaskgroupid(c.getInt(c.getColumnIndex("groupid")));
			taskgroup.setContent(c.getString(c.getColumnIndex("content")));
			taskgroup.setStartdate(c.getInt(c.getColumnIndex("startdate")));
			taskgroup.setEnddate(c.getInt(c.getColumnIndex("enddate")));
			taskgroup.setMemo(c.getString(c.getColumnIndex("memo")));
			taskgroup.setRepeatetype(c.getInt(c.getColumnIndex("repeatetype")));
			taskgroup.setRepeatevalue(c.getString(c
					.getColumnIndex("repeatevalue")));
			taskgroups.add(taskgroup);
		}
		c.close();
		return taskgroups;
	}

	/**
	 * close database
	 */
	public void closeDB() {
		db.close();
	}
}